#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_

import time, sys
sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
#from charts.models import *
import datetime
from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()

#meta = MetaData(db)
#db.metadata.reflect()


def run():
    #从settings.ini获取任务类型编号
    settings = Ini('../apps/settings.ini')

    #assignment_type = dict(settings.PARA.ASSIGNMENT_TYPE)


    req = db.metadata.tables['assignment']

    #获取本月月初和月末时间
    month_first_day,month_last_day,month_view = get_month()

    #统计项目延迟情况数
    #20101227增加where查询条件status = '2'任务状态为延迟
    xmyc = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type,req.c.delay_classes],and_(req.c.status == '2')).group_by(req.c.delay_classes,req.c.kfzx,req.c.type)

    req_model = get_model('assignment_month_report_xmycfx')

    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
    req_model.remove(req_model.c.month_date == month_last_day)

    # 按照任务类型存储统计数据



    xmyc_rs = xmyc.execute()



    #定义字典型data
    data = {}
    #连接kfzx表,根据开发中心id初始化data
    types = ['1','2','3']


    task_stage = dict(settings.PARA.ASSIGNMENT_DELAY_CLASSES)

    req_kfzx_model = get_model('kfzx')
    sel_kfzx = select([req_kfzx_model.c.id])

    for x in types:
    #    print 'x',x
        data[x] ={}
                                                              
    #说明：t，k是用来判断data[in_type][in_kfzx]字典是否已经被初始化
    t = '0'
    k = 0
    for row in xmyc_rs:   
            count,in_kfzx,in_type,in_delay_classes = row
            if not in_delay_classes:
                continue
            in_delay_classes = long(in_delay_classes)
            if k != in_kfzx or t != in_type:
                k = in_kfzx
                t = in_type
                data[in_type][in_kfzx] = {}
    #            print k
            y = 'delay_num_%02d' % in_delay_classes
    #        print y
    #        print in_type,in_kfzx
            data[in_type][in_kfzx]['kfzx'] = in_kfzx
            data[in_type][in_kfzx]['type'] = in_type 
            data[in_type][in_kfzx][y] = count
            data[in_type][in_kfzx]['month_date'] = month_last_day
            data[in_type][in_kfzx]['month'] = month_view
    #        print data

            
    #        data[in_type]['delay_classes'] = in_delay_classes    


    for t,v in data.items():
    #    print 't',t,'v',v
        for x,y in v.items():
            #print x,y
            r = req_model(**y)
            r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise
